﻿using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MedicService.Server.Exception;
using MedicService.Server.Utils;

namespace MedicService.Server {
    public class DatabaseConnector {

        public static DataTable GetData(string tableName) {
            return GetData(tableName, "id");
        }


        public static DataTable GetData(string tableName, string orderBy) {
            return GetData(tableName, orderBy, null);
        }

        public static DataTable GetData(string tableName, string orderBy, string where) {
            return GetData(tableName, orderBy, where, null);
        }

        //TODO: нужна обработка ошибок
        public static DataTable GetData(string tableName, string orderBy, string where, int? limit) {
            //var con = new SqlConnection(ConfigurationManager.ConnectionStrings["medicConnectionString"].ToString());
            var con = new SqlConnection(Constants.ConnectionString);
            var mySqlCommand = con.CreateCommand();
            con.Open();
            if (con.State != System.Data.ConnectionState.Open) {
                throw new DbException();
            }
            string top = "";
            if (limit != null) {
                top = "TOP " + limit;
            }
            mySqlCommand.CommandText = "select " + top + " * from [" + tableName + "]";
            if (where != null) {
                mySqlCommand.CommandText += " WHERE " + where;
            }
            mySqlCommand.CommandText += " ORDER BY " + orderBy;
            var mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            var myDataSet = new DataSet();
            mySqlDataAdapter.Fill(myDataSet, tableName);
            var dataTable = myDataSet.Tables[tableName];

            //foreach (DataRow myDataRow in myDataTable.Rows) {
            //    d.intValue = (int)myDataRow["id"];
            //    d.strValue = myDataRow["login"] + " : " + myDataRow["password"];
            //    break;
            //}

            con.Close();
            return dataTable;
        }

        public static int Insert(string query) {
            string insertedId = "";

            //string query = "INSERT INTO users" +
            //                 " (name, lastname)" +
            //                 " VALUES (@Name, @LastName);" +
            //                 " ";

            using (var dbconn = new SqlConnection(Constants.ConnectionString))
            using (var dbcm = new SqlCommand(query, dbconn)) {
                //var keyEnum = parameters.Keys.GetEnumerator();
                //while (keyEnum.MoveNext()) {
                //}
                //dbcm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
                //dbcm.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;

                dbconn.Open();
                insertedId = dbcm.ExecuteScalar().ToString();
                return insertedId.Length > 0 ? int.Parse(insertedId) : 0;
            }
        }

        internal static void Update(string query) {
            using (var dbconn = new SqlConnection(Constants.ConnectionString))
            using (var dbcm = new SqlCommand(query, dbconn)) {
                //var keyEnum = parameters.Keys.GetEnumerator();
                //while (keyEnum.MoveNext()) {
                //}
                //dbcm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
                //dbcm.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;

                dbconn.Open();
                dbcm.ExecuteNonQuery();
            }
        }

        internal static DataRow GetById(string tableName, int id) {
            var con = new SqlConnection(Constants.ConnectionString);
            var mySqlCommand = con.CreateCommand();
            con.Open();
            if (con.State != System.Data.ConnectionState.Open) {
                throw new DbException();
            }
            mySqlCommand.CommandText = "select top 1 * from [" + tableName + "] where id = " + id + ";";
            var mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            var myDataSet = new DataSet();
            mySqlDataAdapter.Fill(myDataSet, tableName);
            var dataTable = myDataSet.Tables[tableName];
            con.Close();
            return dataTable.Rows.Count >0 ? dataTable.Rows[0] : null;
        }
    }
}